GQL, GQL/Admin, GQL/Design, GQL/User are trademarks of Andyne Computing Limited. All other products mentioned in this guide are trademarked or copyrighted by their manufacturers.
The GQL/Demo contains a special version of the GQL/User application that will allow you to become familiar with GQL concepts and to demonstrate how GQL can be used to access host database information. The demo version differs from the actual product in one major way - you don’t need to be connected to the host computer while running the demo. In other respects, using the GQL/Demo is just like using GQL/User.
If you are not familiar with the GQL family of products you may wish to glance at Appendix A for a brief description of the product family.
These notes will take you through a sequence of steps that introduce GQL/User features to you. Stored results are used to provide data for all of the queries described in this booklet. You may want to deviate from the scripted session. However, since the demo is not accessing a real database management system you won't get actual data back for every query you try. Whenever you generate a query that has no corresponding stored results, GQL will provide simulated data and notify you with a message.
INSTALLATION
The GQL/Demo is distributed on a single high-density (1.4 MB) diskette, but it is recommended that you run the demo from a hard disk. To install, simply drag the GQL Demo Folder onto your hard disk.
Please keep the contents of the GQL Demo Folder together as the demo application requires access to the data files to present query results.
Note: If you see the message: "This disk is unreadable: Do you want to initialize?", you are probably trying to install the demo on a system that can't read 1.4MB floppy disks. Use another system to copy the demo onto two 800KB floppy disks, or give us a call and we will send you 800KB floppy disks.
GETTING STARTED
Start the demo by double-clicking on USA Sales Information. This will launch the application GQL/Demo and open the USA Sales Information data model.
The GQL desktop window will appear.
The data model window contains data objects, relationships, executive buttons, and ornaments. The square and rectangular icons are data objects that represent tables that contain information in the database. Data objects are connected to each other by lines that contain relationship (diamond shaped) icons. Relationships guide the user through the database so that information can be extracted from more than one data object. Relationships are named in a manner that helps the user understand how the information in one data object can be combined with information from another. However, in some cases you may choose not to show the relationship names, or even display the relationship icons, if you feel it isn't required. In this example the customer data object is related to the order data object through the placed relationship. The order data object is related through the sold relationship to the salesman data object. This approach to the data model guides the user in getting information about the customer and the sales staff by showing the different ways that the information can be related. The user formulates queries by using the data objects and relationships.
Executive buttons are used to provide easy push button access to information in the corporate database. Each button can be associated with a stored query (with optional parameters) and an output (a Query Results window, a report, another application, or a file), or can be used to activate another window. It is possible to have buttons that directly activate other applications such as Microsoft Excel to have the data charted or placed into a spreadsheet. Stored queries can be formulated by the user or created for the user by the MIS staff.
Ornaments are text or graphic objects that are placed on the data model as titles, borders, comments, highlights, etc. in order to improve the layout.
AD HOC QUERY TOOL OR EIS APPLICATION?
GQL does an excellent job at both! The GQL product family includes a special tool (GQL/Admin) for customizing the interface to meet your needs. Using GQL/Admin, the graphical data model is created, attributes can be renamed to make them more meaningful for the user, customized sets of executive buttons can be created, and the overall layout is defined. The resulting GQL document can be setup as a multi-level executive button interface, an ad hoc query interface, or both. The data model can be hidden from users if you wish to create a push button only user environment. GQL has the unique capability of allowing you to customize the interface to suit the different needs and skill levels of different users.
The USA Sales Information document is a combination of both ad hoc and EIS (Executive Interface System) styles. It is designed to give you a feel for the overall product. This demo guide starts by taking you through an EIS style interface, shows you the integrated report module, and then moves on to ad hoc queries and executive buttons. Once you have gone through the demo you will also want to look through the menus and experiment to discover some of the other powerful features in GQL.
EIS (EXECUTIVE INFORMATION SYSTEM)
GQL can be customized to provide an executive interface system (EIS) style interface. This demo includes a drill-down interface that demonstrates some of these capabilities.
Begin at the USA Sales Information window and click the executive button with the map of the USA on it.
The map of the USA is divided into three sales regions. There are several executive buttons displayed that either take you to other windows, or issue stored queries. Clicking the executive button for a region will 'zoom in' on that region by moving to another window that has regional details. In this example we will look at the Western sales region.
Click the button called Western Region.
A map of the Western sales region is shown on the left side of the window. The small USA map in the upper right corner has executive buttons with arrows that are used to switch to other regions or return to the main USA map. The button in the lower right corner is used to switch back to the main window for ad hoc queries The other two executive buttons issue stored queries. We will use the Customer List button to ‘drill-down' to specific information about customers in San Jose, California. GQL provides an extensive prompting facility that allows you to enter qualifying information before a query is issued.
Click the Customer List button.
The dialog that appears allows you to enter additional information that qualifies or restricts the information that will be retrieved when you submit the query. You can enter information by typing directly into the prompt boxes or by using data values to select from a list of known values. The values that are initially displayed in the prompt boxes are the last values that were entered. We will use two different ways to access stored values for the prompts. A button with a down arrow is located at the right of the State prompt. Click and hold down the mouse button while the cursor is over this button, and a pop-up list of values will appear as follows:
Choose CA (for California) from the list. The value for the State prompt will be set to CA and the value for the City prompt will be highlighted (in preparation for your next entry). In the next example we will actually issue a query to load a list of valid values from the database. The query that retrieves the list of values for City uses the State information you just entered to retrieve only names of cities in California that are stored in the database.
With the City prompt value highlighted, click the Data Values... button..
Select San Jose from the list that appears and click Insert (or double-click on San Jose). Now that you have entered the qualifications, click the OK button and the query will be submitted.
You have just completed your first query! Using the facilities provided by GQL you were able to 'drill-down' to the specific information we wanted.
Return to the USA Sales Information window by clicking the executive button that has a data model diagram on it or by selecting USA Sales Information from the Window menu.
INTEGRATED REPORT MODULE
GQL’s report module features an interactive (WYSIWYG) interface that also permits end users to create their own reports using up-to-the-minute database information. Frequently used reports can be stored with executive buttons.
Click the executive button with the picture of a report on it.
A stored query will be issued. After the results are returned, the report will be displayed.
AD HOC QUERIES
The USA Sales Information data model describes a database containing customers, orders and sales staff. Let’s make your first ad hoc query to find out who the customers are. Close the Report and Query Results windows and choose New Query from the Query menu.
Double-click on the customer icon to open the customer attribute window.
You will want to list the customer names and their cities. Click on the attribute labeled Name. Note how Name is changed to bold face and a "•" character is put in front to indicate that this field will be included in the results. Click on the attribute labeled City to include it in the query as well.
Submit the query by clicking the Submit Query button at the lower left corner of the window (or by choosing Submit Query from the Query menu).
As data is returned from the host it will fill the window. The actual number of the Query Results window will vary depending on how many queries you have tried in the previous section of the demo.
THE RESULTS WINDOW
The data for your query is displayed in the Query Results window. This window presents the data in a spreadsheet-like form. Individual cells or groups of cells can be selected directly from the Query Results window for copying to other applications through the Macintosh Clipboard facility.
Before sending data to another application, you may want to rearrange the data or manipulate it in various ways. Many operations on the local query results are supported by GQL. For example, the data in the Query Results window can be sorted locally (without going back to the host database), ranges taken on the data, columns reordered or completely hidden, or the columns re-sized.
To re-size a column, simply move the cursor to the dividing line between a column and its neighbor.
Drag the line right to widen the column or left to narrow the column.
An alternative method of setting column widths is to click on a column name to select a column and then choose Set Column Width… from the Results menu. A dialog is displayed that allows you to enter the desired width for this or all columns or select an option to automatically set the width to the widest item or to the default size (approximately 15 characters).
Other options are available with full menus enabled (full menus are discussed later).
The Filter… button located at the bottom left corner of the Query Results window activates local operations for sorting, range selection and computing minimum, maximum, sum and averages on columns.
Click the Filter… button to open the Filter Results dialog.
Click the Sort... button to open a dialog for selecting the sort order.
Click on City in the Selected Columns list, then click «Move» (City will move to the Sort Order column). Click the First button to sort the results first by City. Click the OK button. This specified that the data should be sorted in ascending order according to City. The filter dialogs will disappear, and a second results window, Query Results 1.1, will be displayed. GQL creates a second window so that you can refer to the original results from the query. You can manipulate the data locally without concern of losing the original data.
The data in any Query Results window can be saved to a file and subsequently read into other applications. You can even save the data with another file type so that double-clicking the document will open the desired application.
QUALIFICATIONS AND SORTING
As is often the case with an ad hoc query, you may decide to change the query once you’ve seen the data. In this case you may decide you want to have the companies in alphabetical order, and you only want to look at customers from Illinois or Indiana.
An important feature of GQL is its support for incremental query formulation. You can go back to the query you just submitted and change it to reflect your new goals.
Let’s go back to the customer attribute window. The Window menu lists all the GQL windows currently open. Choose customer from the Window menu.
Note: if customer doesn’t appear in the list then you may have closed the window earlier. In that case, select the window named USA Sales Information to get back to the data model window. Double-click on the customer icon to reopen the customer attribute window.
To the right of each attribute name are four buttons. The fourth column of buttons are the Sort buttons. Click the one beside Name to sort the results by Name. A numeral 1 will appear in the button to indicate that this is the primary sort field (i.e. sort the results so that the companies are in alphabetical order).
To restrict the results to those companies in Illinois or Indiana, we will add two qualifications (restrictions) to the query.
The second column of buttons (in the middle) are the Qualify buttons. Click the one beside State to indicate that you want to add a qualification. A check mark will appear in the button to indicate that there is a qualification on the field.
At the bottom of the customer attribute window a qualification node for state will be displayed with a text insertion point. Simply type IL, the state code for Illinois. (This must be upper case.)
But we wanted two conditions (Illinois or Indiana), so click the Qualify button beside State again. Type IN, the state code for Indiana. (Also in upper case.)
There is one more step. Right now the qualification requires that the customer’s state code be IL and IN; we want IL or IN. Simply click on the word and between the two qualification nodes; it will change to or.
Now click the Submit Query button to get the new results!
FULL MENUS
GQL can be set to use either short menus or full menus. Short menus provide a simplified interface that meets the needs of many users, but in order to use some of the advanced features in GQL you will need to enable full menus. Before proceeding with the next section of the demo, select Full Menus from the Edit Menu.
DATA VALUES
We’ll start a new query. First close the Query Results window from the previous query (click on the go away box), then select New Query from the Query menu. This clears out the previous query so we can start from scratch.
Now let’s get information on Nicolo Corporation.
Double-click the customer data object to open the customer attribute window. Choose Name, Address 1, City, and State as the attributes for the query.
If we submit the query now we’ll get all the customers - not just Nicolo Corporation. Let’s put the qualification on company name by clicking on the Qualify button beside the Name attribute. Type Nicolo Corporation.
Click the Submit Query button. The query returns with an alert saying that no data matched our query.
Sometimes you want to put a qualification on a query but you don’t know the value exactly as it occurs in the database. Nicolo Corporation may not be the exact name as it appears in this database. We’ll use the Data Values facility to get the value as it is in the database.
Click OK to display the customer attribute window. Select the text of the qualification (leaving the quote marks out of the selection).
Type the backspace or delete key to delete Nicolo Corporation.
Select Insert Data Values… from the Edit menu. The Data Values dialog will appear.
The customer names have filled the data value list. Type N to scroll down to the Ns. Nicolo Corporation will be selected.
Click the Insert button to have the value copied into the customer name qualification. Now we see why our first attempt didn’t work - the actual value in the database was Nicolo Corp.
Now click the Submit Query button, and voilà, the information on Nicolo Corporation!
USING RELATIONSHIPS
This query now gets the information we wanted about a particular customer. Let’s now find out about the orders that this customer has placed. Reactivate the USA Sales Information window. There is a relationship between customer and orders called ‘placed’. Click once on the placed icon (the diamond). The connecting line from customer to placed and the line from placed to orders are highlighted. We have just activated the relationship between customer and orders.
Now double-click on order. (If you forget to activate the relationship before you try to open another data object, GQL warns you that you have forgotten the relationship. Simply Cancel the alert and go back to put the relationship in.) The order attribute window is open. Select Amount and Date Required. (Notice that you’re dealing with attribute names like Date Required rather than cryptic database names like req_date.)
Now submit the query. The information on Nicolo Corporation now includes the current orders.
SAVING QUERIES (QUERY DOCUMENTS)
The query we just executed may be one that you want to be able to use again in the future. Let’s save it so we can use it later.
Select Save Query… from the Query menu. Specify a name for this query (say Nicolo) and click the Save button. This will save the query as part of the USA Sales Information document. Should you want to exchange this query with another user, or provide this query as a Data Access Manager (DAM) query document to another application that uses the Data Access Manager High Level Interface, you could have chosen to save the query in a separate document (using the To File… button).
Now the query is available for use at anytime in the future. Simply choose the Load Query… from the Query menu to have the saved query reloaded. Of course the query can be edited or extended once it is reloaded. You’ll have to save it again if you want the edited query to be stored in place of the original.
Note: In the GQL/Demo, the ability to save documents has been disabled. Any queries you save will be available only during the current session.
EXECUTIVE BUTTONS
Saved queries are very useful, but some queries are used so regularly that you may want even easier access to them. GQL’s executive buttons provide a convenient way to organize and access your standard queries.
The USA Sales Information data model has several executive buttons already defined. Return to the USA Sales Information window.
Click the button labeled Customer List. GQL will load the query associated with this button and submit the query. The results will be returned in a Query Results window.
Now let’s create a button for our own query. Return to the USA Sales Information window.
Select Create Button… from the Layout menu. The Create Button dialog will appear.
There are three steps in creating an executive button. First specify the appearance of the button; second, specify the linkage to a query or window; and third, specify the output destination for the results.
We’ll create a text button. Click the Edit Text... button.
Type Nicolo Orders and select the font, size, alignment, style, and color that you want. Click OK to return to the Create Button dialog.
The linkage is set to Query by default. To select the actual query, click and hold down the mouse button while the cursor is over <Current Query> beside Select Query. A pop-up list of available queries is displayed. Choose the Nicolo query you created earlier. (You can also select<Current Query> to automatically link the button to the query you are currently working with.)
Click OK to return to the Create Button dialog.
Leave the Output section for now - the results will be sent directly to a GQL Query Results window.
Click OK to return to the USA Sales Information window.
You will find the button you have just created selected in the window. Drag it to a convenient location, then click the mouse button when it is not over the executive button in order to de-select the button. It’s ready to use! Just click it to execute the query.
Note: If you want to edit the button specification, or move it elsewhere in the diagram, select Edit Mode from the Layout menu. While in Edit Mode you can access the Create Button dialog by double-clicking the button and move it by dragging it with the hand cursor.
QUALIFICATION PROMPTS
The query we created and saved with a button was useful for getting the sales to Nicolo but not as useful for other companies (although it can always be loaded, the qualification edited, and the revised query submitted). Let’s generalize the query so that it will allow us to enter the company name whenever the query is selected. We will use GQL’s qualification prompt facility.
There’s no sense starting from scratch, so let’s load the Nicolo query. Select Load Query… from the Query menu. Select Nicolo from the list and click Load. The query is now loaded and ready for modification; the query window named Query: Nicolo is displayed. Return to the USA Sales Information window. Double-click on the customer icon to open the customer attribute table. In the qualification area select the text Nicolo Corp. Press backspace or delete to clear the text and just leave the quote marks. Now select Insert Prompt… from the Edit menu.
We’ve already arranged to create the prompt for you so select the Prompt Id company. Click the Insert Prompt button to have the special qualification prompt inserted into the qualification for you.
Now test the query by clicking the Submit Query button. GQL prompts you for the company name. Obviously you could type the name if you know it, but it’s easier to use the Data Values button to get at the values from the database. Use the Data Values dialog box to select Acme Manufacturing. Click Insert, then click the OK button to execute the query and get Acme’s orders.
Qualification prompts provide a mechanism to create generalized query templates that you complete at the time the query is to be submitted. There can be more than one qualification prompt in each query. They can be used in many contexts in the query to achieve the results you want. The value last used for a qualification prompt is remembered by GQL in the event you want to reuse the value in several related queries.
GETTING STARTED WITH SYSTEM 7.0
This section is provided for those who are familiar with GQL and simply want to explore the Macintosh System 7.0 specific features.
Data Access Manager: GQL supports use of the System 7.0 Data Access Manager and DDEVs such as Data Access Language and Butler.
Publish/Subscribe: GQL/User allows executive buttons to publish query results. (To edit existing buttons select Edit Mode from the Layout Menu). Once the publisher is created and the GQL document is saved, the linkage is maintained by GQL. Each time that button is pressed, the query is issued and the Edition file is rewritten with new results.
Query Documents: When you save a GQL query in a file GQL creates a Data Access Manager Query Document. These query documents can be used by other applications that support the Data Access Manager High Level Interface.
Balloon Help: GQL/User has Balloon help for all its menus and the data objects in the data model window.
AppleEvents: GQL currently supports the required AppleEvents.
PUBLISH/SUBSCRIBE (USING GQL AND EXCEL)
Query results are often most useful when they are exported from GQL into other Macintosh applications. GQL supports export of query results in several ways. Data can be selected in the Query Results window and copied into the Clipboard. (All the data can be selected by choosing Select All from the Edit menu or by clicking on the row count in the upper left part of the results window.) From the Clipboard the data can be pasted into any application (try the CMD-Shift-V power user paste).
Query results can be saved to a file. The file can be read by most applications. To save a results file select Save Results… from the Results menu.
Executive buttons can be set up to save results directly to a file, directly to another application through the Clipboard, or to Publish an edition file that can be subscribed to by other 7.0 savvy applications. Let’s see how the executive button feature can be set up with a specific application such as Excel.
You must have Microsoft Excel 3.0 to run the next demonstration.
In the Finder locate the Pub/Sub Demo document. Launch Excel by double-clicking on the Excel document labeled Pub/Sub demo. This will open the Excel spreadsheet that has been set up for this demo.
When you first open the Pub/Sub demo document the area designed for the subscriber information is already selected. To select the edition file containing published information from GQL, choose Subscribe to... from the Edit menu (with the initial selection active). When the dialog appears, select Sales amounts from the GQL V3.0 Demo Folder and click the Subscribe button. (If you now save the Pub/Sub demo document you won’t have to do this setup again.)
If your Macintosh has enough memory you can run GQL and Excel at the same time. This way you will be able to see the chart update automatically in Excel when an executive button is pressed in GQL.
Leave Excel running and return to the demo.
Click the button with the icon of the bar chart on it.
This button executes a query that retrieves all orders over a certain amount. The button was specified to publish its results to an Edition file named Sales Amounts. This is specified by the Publish operation in the Output section of the Create Button dialog.
You may be prompted for the name of the edition file. If so, select Publish to OK the default name (Sales amounts).
The Excel document subscribes to that edition and uses the information to draw two charts. Try the query with 5000 and with 30000 as the minimum and maximum amounts. You will see the charts automatically updated by Excel.
The effect of the executive button is to draw the appropriate chart. Of course, each button can have a separate effect, customized to your requirements.
OTHER FEATURES
This demo highlights the main features of GQL, but there are lots of other capabilities. You can explore some of them by experimenting with the other menu items. Some things you may discover are:
Aggregate functions (SUM, AVG, etc.)
Grouping
Dynamic relationships
Full/Short menus
Combining qualifications
Preferences
Color support (including preferences)
Icon and PICT executive buttons and ornaments
Annotations
Submit query to file
Results options
Things to try...
Reports: from the Query Results window you can generate formatted reports using commands from the Report menu. You must have selected Full menus (from the Edit menu) and then chosen Show Report from the Report menu.
Quick Select of Attributes: with the cursor over a data object in the data model, click and hold the mouse button. A pop-up list of attributes will be displayed for easy selection.
Form View: in the Query Results window you can double-click on any row number to switch into Form view. The Form view shows all columns for a single row and allows you to step through the results one row at a time.
Large Column Display: in the Query Results window, long text strings can be truncated at the column boundary. You can see the whole string by double-clicking a cell; an expanded view will be displayed.
Automatic Column Width: there is a check box in the column width dialog that will automatically re-size columns to the largest size required for the current data.
Keep in mind that GQL is a very versatile tool. It can be used by experienced Information Systems professionals to create query environments for their end-users or themselves, and it can be used by end-users who want access to the data but don’t have training with relational database concepts. GQL can be customized to meet the different skill levels and requirements of different users.
BUILDING GQL DATA MODELS
You may be wondering where the data model document comes from. The data models are created by the application GQL/Admin. The task of building the data model for a user is usually done by a member of the IS staff who knows the structure of the database and who can customize the data model for users. GQL/Admin makes it very easy to add value to the database for the end-user. By providing effective end-user access to corporate data the IS staff is freed from routine data requests and can concentrate on the more difficult tasks for which their specialized training and experience are of critical importance.
GQL — THE CORPORATE DATA ACCESS SOLUTION
GQL is the ultimate solution for corporate database access. It enables end-users to access one of the corporation’s most valuable assets - information previously locked in corporate databases.
GQL supports the goals of professionals who are striving to provide access to the corporate database. It allows them select the information to be presented to the users, and to take advantage of their specialized knowledge to create customized data models and EIS solutions for individuals or departments. It helps them fulfill their mandate to provide users with information, not just data.
The GQL tools provide significant productivity gains, both for end-users and for the IS staff that supports end-users. GQL delivers a true win-win solution for progressive information-oriented organizations.
APPENDIX A -- THE GQL PRODUCT FAMILY
The GQL family of products includes GQL/User, GQL/Admin, GQL/Design, and GQL/Update.
GQL/User provides decision makers with an intuitive and graphical view of database information customized to individual, departmental, or corporate needs. GQL's graphical approach to data access allows users to quickly locate, retrieve, or update information critical to their needs. All query results are displayed in a familiar spreadsheet format and can be stored locally. This data can then be searched, sorted, or seamlessly integrated with other applications, including word processors, presentation packages, and spreadsheets such as Excel or Wingz. GQL’s executive button facility allows organizations to create fully customized data access environments for the widest possible range of users. Using GQL, organizations are free to design user interfaces consisting entirely of executive buttons, as well as those containing customized database models for satisfying ad hoc query and reporting needs. GQL supports both Windows DDE facilities and Macintosh System 7 features.
GQL/Admin allows the database administrator, MIS personnel, or database application staff to develop flexible and sophisticated query environments for the widest possible range of users.
The administrator begins by loading the tables directly from the host system in order to create the data objects used in the data model. Using GQL/Admin, the administrator can rename data objects and attributes to make them more meaningful to users and develop an attractive and intuitive layout of customized data models for use with GQL/User. The administrator or application developer can also store pre-defined queries as executive buttons to provide users with database applications tailored to their needs. And because GQL works transparently with popular programs such as Excel and Wingz, database application developers can use GQL/Admin to create sophisticated decision support systems that include integrated charting and data analysis capabilities.
GQL/Design provides database designers with a rapid prototyping tool for creating new databases and data models for use in testing and developing sophisticated query applications. Using GQL's sophisticated graphical interface, the designer first creates the data model locally, then generates the corresponding database tables on the host computer or stores the create commands locally as a script file. The resulting data model can then be used directly with GQL/User. GQL/Design supports the entity relationship design method and includes built-in design checks.
GQL/Update is an extended version of GQL/User that allows users with special access privileges to perform data entry and table maintenance functions.
WINDOWS
GQL for Windows supports both the Dynamic Data Exchange (DDE) and Object Linking and Embedding (OLE) technologies. These advanced data sharing and inter-application communication facilities allow developers to use GQL to create sophisticated office automation and decision support systems for a wide range of business, academic, and technical applications.
UNIX/MOTIF
GQL for Motif will support X/Windows OSF Motif on a variety of Unix platforms. As with GQL for Macintosh and Windows, Unix support will enable end users to share data models, query results, and reports quickly and easily.
